/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Servidor;

import Entidades.Alumno;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutput;
import java.io.ObjectOutputStream;
import java.net.Socket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author imarban
 */
public class ServerAlumno extends Thread{
    
    String driver = "com.mysql.jdbc.Driver";
    String usuario = "root";
    String clave = "root";
    String urlDB = "jdbc:mysql://localhost/Escuela";
    
    private Socket socketCliente = null;
    ObjectOutputStream oos = null;
    ObjectInputStream ois = null;
    int codigoOperacion;
    
    Connection cnn;
    PreparedStatement pst;
    ResultSet rs;
    ResultSetMetaData rsMetaData;
    
    public ServerAlumno(Socket socketCliente, ObjectInputStream ois, ObjectOutputStream oos){
        this.socketCliente = socketCliente;
        System.out.println("Inicialice el serverAlumno");
        try {
            this.oos = oos;
            this.ois = ois;            
            } catch (Exception ioe) {
            System.out.println(ioe.getMessage());
            throw new RuntimeException();
        }start();
    }

    @Override
    public void run() {
        try {
            codigoOperacion = ois.readInt();
            System.out.println("Lei un codigo de " + codigoOperacion);
            switch(codigoOperacion)
            {
                case Constantes.AGREGAR:
                    agregarAlumno();
                    break;
                case Constantes.MODIFICAR:
                    modificarAlumno();
                    break;
                case Constantes.ELIMINAR: 
                    eliminarAlumno();
                    break;
                case Constantes.CONSULTAR_UNO: 
                    enviarAlumno();
                    break;
                case Constantes.CONSULTAR_TODOS: 
                    enviarListaAlumnos();
                    break;
            }
        } catch (IOException e) {
        }
        
           
    } 
    
    private void enviarAlumno()
    {
        int idAlumno;
        Alumno alumno;
        conectarBD();
        
        try{
            idAlumno = ois.readInt();
            pst = cnn.prepareStatement("SELECT * FROM Alumno WHERE idAlumno = ?");
            pst.setInt(1, idAlumno);
            rs = pst.executeQuery();
            rs.first();
            alumno = new Alumno(rs.getInt(1), rs.getString(2), rs.getString(3), 
                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), 
                        rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11));
            
            oos.writeObject(alumno);
            oos.flush();
        } catch (IOException | SQLException  e){
            
        }
    }

    private void enviarListaAlumnos() {
        List<Alumno> alumnos = new ArrayList<Alumno>();
        conectarBD();
        try {
            pst = cnn.prepareStatement("SELECT * FROM Alumno");
            rs = pst.executeQuery();            
            while(rs.next())
            {
                alumnos.add(new Alumno(rs.getInt(1), rs.getString(2), rs.getString(3), 
                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), 
                        rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11)));
            }
            oos.writeObject(alumnos);
            oos.flush();
        } catch (SQLException | IOException e) {
        }       
    }

    private void agregarAlumno() {
        
        conectarBD();
        try {
            pst = cnn.prepareStatement("INSERT INTO Alumno VALUES (null,?,?,?,?,?,?,?,?,?,?)");    
        } catch (SQLException sqle) {
        }
  
        try {
            Alumno alumno = (Alumno)ois.readObject();
            
            pst.setString(1, alumno.getNombre());
            pst.setString(2, alumno.getPaterno());
            pst.setString(3, alumno.getMaterno());
            pst.setString(4, alumno.getEmail());
            pst.setString(5, alumno.getCalle());
            pst.setString(6, alumno.getNumero());
            pst.setString(7, alumno.getColonia());
            pst.setString(8, alumno.getDelegacion());
            pst.setString(9, alumno.getEntidad());
            pst.setString(10, alumno.getTelefono());
            
            pst.executeUpdate();
            oos.writeBoolean(true);
        } catch (ClassNotFoundException | IOException  | SQLException cnfe) {
            try {
                oos.writeBoolean(false);
            } catch (IOException e) {
            }
            System.err.println(cnfe.getMessage());
            throw new RuntimeException();
        } finally{
            try {
                oos.flush();
            } catch (Exception e) {
            }
        }
        
    }
    
    private void modificarAlumno()
    {
        Alumno alumno;
        conectarBD();
        try {
            alumno = (Alumno) ois.readObject();
            pst = cnn.prepareStatement("UPDATE Alumno SET nombre =  ?, paterno = ?, materno = ?, email = ?, calle = ?,"
                    + "numero = ?, colonia = ?, delegacion = ?, entidad = ?, noTelefono = ? WHERE idAlumno = ?");
            
            pst.setString(1, alumno.getNombre());
            pst.setString(2, alumno.getPaterno());
            pst.setString(3, alumno.getMaterno());
            pst.setString(4, alumno.getEmail());
            pst.setString(5, alumno.getCalle());
            pst.setString(6, alumno.getNumero());
            pst.setString(7, alumno.getColonia());
            pst.setString(8, alumno.getDelegacion());
            pst.setString(9, alumno.getEntidad());
            pst.setString(10, alumno.getTelefono());
            pst.setInt(11, alumno.getId());
            
            pst.executeUpdate();
            
            oos.writeBoolean(true);
            oos.flush();
        } catch (IOException | ClassNotFoundException | SQLException ioe) {
            try {
                oos.writeBoolean(false);
                oos.flush();
            } catch (Exception e) {
            }
            System.err.println(ioe.getMessage());
            throw new RuntimeException();
        }

    }
    
    private void eliminarAlumno(){
        int idAlumno;
        conectarBD();
        try {
            idAlumno = ois.readInt();    
            System.out.println("Recibi el identificador del alumno " + idAlumno);
            pst = cnn.prepareStatement("DELETE FROM Alumno WHERE idAlumno = ?");
            pst.setInt(1, idAlumno);
            pst.executeUpdate();
            oos.writeBoolean(true);
            oos.flush();
        } catch (IOException | SQLException ioe) {
            try {
                oos.writeBoolean(false);
                oos.flush();
            } catch (Exception e) {
            }
            System.err.println(ioe.getMessage());
            throw new RuntimeException();
        }
    }
        
    private void conectarBD()
    {
         try {
            Class.forName(driver);
            cnn = DriverManager.getConnection(urlDB, usuario, clave);
         } catch (SQLException | ClassNotFoundException sqle){
             System.err.println(sqle.getMessage());
         }
    }
    
    
        
}
